//插入小纸条
const insert_note = "insert into smart_note(sender_id,receiver_id,note_content,date) values (?,?,?,?)";

const select_note = "select smart_note.id,nickName as senderName,avatarUrl as senderavatarUrl,note_content,date from smart_note left join smart_user on smart_note.sender_id=smart_user.id where smart_note.id = ?";

//获取发送者昵称
const getUser_name = "select nickName from smart_user where id = ?";

//获取接收者openId
const getReceiver_openId = "select openid from smart_user where id = ?";

//查找房东的user_id和open_id
const getHousehold = "select smart_user.id as houseHold_userIds,smart_user.openid as houseHold_openId from smart_tenant left join smart_house on smart_tenant.house_id=smart_house.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id left join smart_user on smart_household_owner.user_id=smart_user.id where smart_tenant.user_id = ?";

//查看我发送的小纸条
const select_myNote = "select smart_note.id as id,smart_user.nickName as receiver_nickName,smart_note.note_content,smart_note.date from smart_note left join smart_user on smart_note.receiver_id=smart_user.id where smart_note.sender_id = ? limit ? offset ?";

const select_userId = "select smart_household_owner.user_id as houseHold_userId from smart_house left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where smart_house.id = ?";

module.exports = {
    insert_note,
    select_note,
    getUser_name,
    getReceiver_openId,
    getHousehold,
    select_myNote,
    select_userId
}